#include "config.h"

#ifdef ENABLE_POSTGRESQL

#include <libpq-fe.h>
#include <iostream>
#include <map>
#include <cstdlib>


#include <boost/lexical_cast.hpp>

#include "PGSQLQuery.h"
#include "PGSQLDatabase.h"

using boost::lexical_cast;

/**
 *
 **/
PGSQLDatabase::PGSQLDatabase(PString host, PString port, PString database, PString username, PString password) : AbstractDatabase(host, port, database,username, password)
{

    this->dbDriverName = new String("PostgreSQL");
    this->isConnected(*database);
    //this->conn = NULL;
}

/**
 *
 **/
PGSQLDatabase::~PGSQLDatabase()
{
    MyPGconnMap::const_iterator end = this->connections.end();
    for (MyPGconnMap::const_iterator it = this->connections.begin(); it != end; ++it)
    {
        PQfinish(it->second);
    }
}


/**
 *
 **/
bool PGSQLDatabase::isConnected(String database)
{
    PGconn* conn = NULL;
    MyPGconnMap::iterator iter = this->connections.find(database);

    if (iter != this->connections.end() )  {
        conn = iter->second;
    }
    else
    {
        conn = PQsetdbLogin(this->host->c_str(), this->port->c_str(), NULL, NULL, database.c_str(), this->username->c_str(), this->password->c_str());
        this->connections.insert(MyPGconnMap::value_type(database, conn));
    }

    if(PQstatus(this->connections[database]) == CONNECTION_OK)
    {
        return true;
    }
    else
    {
        std::cout << "Database Error: " << PQerrorMessage(conn) << std::endl;

        if(conn != NULL || PQstatus(conn) == CONNECTION_BAD) PQfinish(this->connections[database]);
        this->connections.erase(database);
        return false;
    }
}

/**
 *
 **/
void PGSQLDatabase::rename(String oldname, String newname)
{
    //this->query)new PGSQLQuery(this, ALTER TABLE oldname RENAME TO newname;*/
}

/**
 *
 **/
PQuery PGSQLDatabase::query(PString database, String sqlQuery)
{
	return new PGSQLQuery(this, database, &sqlQuery);
}

/**
 *
 **/
PQuery PGSQLDatabase::query(String sqlQuery)
{
	return this->query(this->database, sqlQuery);
}

/**
 *
 **/
PQuery PGSQLDatabase::select(PString database, PString schema, PString table, int option)
{
    TableInfo info = this->getTableInfoOf(database, schema, table);
    String query = "SELECT ";


    if( (option & OPTION_ONLY_ATTR || option & OPTION_ALL ) && info.fields.size() != 0)
    {
        query.append(info.fields.at(0).name);
        for(unsigned int i = 1; i < info.fields.size(); i++)
        {
            query.append(","+info.fields.at(i).name);
        }
    }
    if(info.hasGeometry && (option & OPTION_ONLY_GEOM || option & OPTION_ALL) )
    {
        if(option & OPTION_ONLY_ATTR || option & OPTION_ALL) query.append(",");
        query.append("AsText("+info.geometry.name+")");
    }

    query.append(" FROM "+*schema+"."+*table);
    return this->query(database, query);
}

/**
 *
 **/
PQuery PGSQLDatabase::getFieldListQuery(PString database, PString schema, PString table)
{
    String query = "SELECT c.COLUMN_NAME, CASE WHEN lower(c.UDT_NAME) = 'geometry' THEN lower(c.UDT_NAME) ELSE c.DATA_TYPE END AS DATA_TYPE FROM information_schema.COLUMNS as c WHERE c.TABLE_CATALOG='"+*database+"' AND c.TABLE_SCHEMA = '"+*schema+"' AND c.TABLE_NAME = '"+*table+"'";
    return this->query(database, query);
}

/**
 *
 **/
PQuery PGSQLDatabase::getTableInfoQuery(PString database, PString schema, PString table)
{
    String sqlQuery = "SELECT CASE WHEN (SELECT COUNT(geo1.*) FROM "+*schema+".geometry_columns AS geo1 WHERE geo1.f_table_schema = '"+*schema+"' AND geo1.f_table_name = '"+*table+"') > 0 THEN 'YES' ELSE 'NO' END AS has_geometry, CASE WHEN (SELECT COUNT(geo2.*) FROM "+*schema+".geometry_columns AS geo2 WHERE geo2.f_table_schema = '"+*schema+"' AND geo2.f_table_name = '"+*table+"') > 0 THEN CASE WHEN (SELECT geo3.srid FROM public.geometry_columns AS geo3 WHERE geo3.f_table_schema = '"+*schema+"' AND geo3.f_table_name = '"+*table+"') != -1 THEN 'YES' ELSE 'NO' END ELSE 'NO' END  AS has_srid, pg_total_relation_size('"+*schema+"."+*table+"') as table_size, (SELECT COUNT(*) FROM "+*schema+"."+*table+") as data_size";
	/*"SELECT "
	" CASE WHEN (SELECT COUNT(geo1.*) FROM "+*schema+".geometry_columns AS geo1 WHERE geo1.f_table_schema = '"+*schema+"' AND geo1.f_table_name = '"+*table+"') > 0 THEN "
		"'YES'"
	" ELSE "
		"'NO'"
	" END AS has_geometry, "
	" CASE WHEN (SELECT COUNT(geo2.*) FROM "+*schema+".geometry_columns AS geo2 WHERE geo2.f_table_schema = '"+*schema+"' AND geo2.f_table_name = '"+*table+"') > 0 THEN"
		" CASE WHEN (SELECT geo3.srid FROM public.geometry_columns AS geo3 WHERE geo3.f_table_schema = '"+*schema+"' AND geo3.f_table_name = '"+*table+"') != -1 THEN "
			"'YES'"
		" ELSE "
			"'NO'"
		" END "
	" ELSE "
		"'NO'"
	" END  AS has_srid,"
	"pg_total_relation_size('"+*schema+"."+*table+"') as table_size,"
	"(SELECT COUNT(*) FROM "+*schema+"."+*table+") as data_size";*/
	return this->query(database, sqlQuery);
}

/**
 *
 **/
PQuery PGSQLDatabase::listDatabases()
{
    String sqlQuery = "SELECT datname FROM pg_database WHERE datname NOT IN('template0', 'template1')";
	return this->query(sqlQuery);
}

/**
 *
 **/
PQuery PGSQLDatabase::listSchemata(PString database) {
    String sqlQuery = "SELECT distinct table_schema FROM information_schema.tables WHERE table_schema <> 'pg_catalog' AND table_schema <> 'information_schema' AND table_catalog = '"+*database+"'";
    return this->query(database, sqlQuery);
}

/**
 *
 **/
PQuery PGSQLDatabase::listTables(PString database, PString schema)
{
    String sqlQuery = "SELECT table_name FROM information_schema.tables WHERE table_schema='"+*schema+"' AND table_catalog = '"+*database+"' AND table_name NOT LIKE 'pg_%' AND table_name NOT LIKE 'sql_%' AND table_name NOT IN('geography_columns','geometry_columns','spatial_ref_sys')";
    return this->query(database, sqlQuery);
}


void PGSQLDatabase::dropTable(PString database, PString schema, PString table)
{
    String sqlQuery = "DROP TABLE IF EXISTS \""+*schema+"\".\""+*table+"\"";
    PQuery query = this->query(database, sqlQuery);
    delete query;
}

/**
 *
 **/
bool PGSQLDatabase::exists(PString database, PString schema, PString table)
{
    if(database->compare("") == 0) return true; // ROOT

    String sql = "select datname from pg_database where datname = '"+*database+"'";

    if(schema->compare("") != 0 && table->compare("") == 0)
    {
        sql = "SELECT distinct table_schema FROM information_schema.tables WHERE table_schema <> 'pg_catalog' AND table_schema <> 'information_schema' AND table_catalog = '"+*database+"' AND table_schema='"+*schema+"'";
    }
    else if(schema->compare("") != 0  && table->compare("") != 0 )
    {
        sql = "SELECT table_name FROM information_schema.tables WHERE table_schema='"+*schema+"' AND table_catalog = '"+*database+"' AND table_name ='"+*table+"'";
    }
    PGSQLQuery query(this, database, &sql);
    return (query.getNumRows() != 0); // 0 = not exists, 1 = exists
}

#endif
